﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class LoadHeaderDA : ILoadHeaderDA
    {
        private string BEGIN_LOAD_TIME = "@BEGIN_LOAD_TIME";
        private string CARRIER_ADDRESS = "@CARRIER_ADDRESS";
        private string CARRIER_CITY = "@CARRIER_CITY";
        private string CARRIER_CONTACT = "@CARRIER_CONTACT";
        private string CARRIER_COUNTRY = "@CARRIER_COUNTRY";
        private string CARRIER_EMAIL = "@CARRIER_EMAIL";
        private string CARRIER_FAX = "@CARRIER_FAX";
        private string CARRIER_ID = "@CARRIER_ID";
        private string CARRIER_NAME = "@CARRIER_NAME";
        private string CARRIER_PHONE = "@CARRIER_PHONE";
        private string CARRIER_PROV = "@CARRIER_PROV";
        private string CARRIER_REF = "@CARRIER_REF";
        private string CARRIER_ZIP = "@CARRIER_ZIP";
        private string COST = "@COST";
        private string CREATED_BY = "@CREATED_BY";
        private string DEPARTURE_TIME = "@DEPARTURE_TIME";
        private string DOOR = "@DOOR";
        private string DRIVER_NAME = "@DRIVER_NAME";
        private string END_LOAD_TIME = "@END_LOAD_TIME";
        private string EXPECTED_CUBE = "@EXPECTED_CUBE";
        private string EXPECTED_UNITS = "@EXPECTED_UNITS";
        private string EXPECTED_WEIGHT = "@EXPECTED_WEIGHT";
        private string EXTERNAL_ID = "@EXTERNAL_ID";
        private string LOAD_BY = "@LOAD_BY";
        private string LOAD_ID = "@LOAD_ID";
        private string LOAD_SEQUENCE = "@LOAD_SEQUENCE";
        private string ROUTE = "@ROUTE";
        private string SQL_CHECK_LOAD_HEADER_ID_UNIQUE = " SELECT COUNT(1) FROM @_@LOAD_HEADER WHERE LOAD_ID = @LOAD_ID ";
        private string SQL_DELETE_LOAD_HEADER = " DELETE FROM @_@LOAD_HEADER WHERE LOAD_ID = @LOAD_ID ";
        private string SQL_INSERT_LOAD_HEADER = " INSERT INTO @_@LOAD_HEADER ( WH_ID, LOAD_ID, EXTERNAL_ID, ROUTE, DOOR, STATUS, CARRIER_ID, CARRIER_NAME, CARRIER_ADDRESS, CARRIER_CITY, CARRIER_PROV, CARRIER_ZIP, CARRIER_COUNTRY, CARRIER_CONTACT, CARRIER_PHONE, CARRIER_FAX, CARRIER_EMAIL, CARRIER_REF, VEHICLE_NUMBER, VEHICLE_TYPE, TRAILER_NUMBER, TRAILER_OWNER, DRIVER_NAME, LOAD_BY, BEGIN_LOAD_TIME, END_LOAD_TIME, DEPARTURE_TIME, TOTAL_CUBE, TOTAL_WEIGHT, TOTAL_UNITS, EXPECTED_CUBE, EXPECTED_WEIGHT, EXPECTED_UNITS, COST, VALUE, LOAD_SEQUENCE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @LOAD_ID, @EXTERNAL_ID, @ROUTE, @DOOR, @STATUS, @CARRIER_ID, @CARRIER_NAME, @CARRIER_ADDRESS, @CARRIER_CITY, @CARRIER_PROV, @CARRIER_ZIP, @CARRIER_COUNTRY, @CARRIER_CONTACT, @CARRIER_PHONE, @CARRIER_FAX, @CARRIER_EMAIL, @CARRIER_REF, @VEHICLE_NUMBER, @VEHICLE_TYPE, @TRAILER_NUMBER, @TRAILER_OWNER, @DRIVER_NAME, @LOAD_BY, @BEGIN_LOAD_TIME, @END_LOAD_TIME, @DEPARTURE_TIME, @TOTAL_CUBE, @TOTAL_WEIGHT, @TOTAL_UNITS, @EXPECTED_CUBE, @EXPECTED_WEIGHT, @EXPECTED_UNITS, @COST, @VALUE, @LOAD_SEQUENCE, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_LOAD_HEADER = " SELECT  ROWID, WH_ID, LOAD_ID, EXTERNAL_ID, ROUTE, DOOR, STATUS, CARRIER_ID, CARRIER_NAME, CARRIER_ADDRESS, CARRIER_CITY, CARRIER_PROV, CARRIER_ZIP, CARRIER_COUNTRY, CARRIER_CONTACT, CARRIER_PHONE, CARRIER_FAX, CARRIER_EMAIL, CARRIER_REF, VEHICLE_NUMBER, VEHICLE_TYPE, TRAILER_NUMBER, TRAILER_OWNER, DRIVER_NAME, LOAD_BY, BEGIN_LOAD_TIME, END_LOAD_TIME, DEPARTURE_TIME, TOTAL_CUBE, TOTAL_WEIGHT, TOTAL_UNITS, EXPECTED_CUBE, EXPECTED_WEIGHT, EXPECTED_UNITS, COST, VALUE, LOAD_SEQUENCE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@LOAD_HEADER ";
        private string SQL_SELECT_ALL_LOAD_HEADER_LANGUAGE = " SELECT  LH.ROWID, LH.WH_ID, LH.LOAD_ID, LH.EXTERNAL_ID, LH.ROUTE, LH.DOOR, LH.STATUS, LH.CARRIER_ID, LH.CARRIER_NAME, LH.CARRIER_ADDRESS, LH.CARRIER_CITY, LH.CARRIER_PROV, LH.CARRIER_ZIP, LH.CARRIER_COUNTRY, LH.CARRIER_CONTACT, LH.CARRIER_PHONE, LH.CARRIER_FAX, LH.CARRIER_EMAIL, LH.CARRIER_REF, LH.VEHICLE_NUMBER, LH.VEHICLE_TYPE, LH.TRAILER_NUMBER, LH.TRAILER_OWNER, LH.DRIVER_NAME, LH.LOAD_BY, LH.BEGIN_LOAD_TIME, LH.END_LOAD_TIME, LH.DEPARTURE_TIME, LH.TOTAL_CUBE, LH.TOTAL_WEIGHT, LH.TOTAL_UNITS, LH.EXPECTED_CUBE, LH.EXPECTED_WEIGHT, LH.EXPECTED_UNITS, LH.COST, LH.VALUE, LH.LOAD_SEQUENCE, LH.UDF1, LH.UDF2, LH.UDF3, LH.UDF4, LH.UDF5, LH.UDF6, LH.UDF7,LH.UDF8, LH.UDF9, LH.UDF10, LH.CREATED_BY, LH.CREATED_DATE, LH.UPDATED_BY, LH.UPDATED_DATE,T1.TEXT AS STATUS_D  FROM  @_@LOAD_HEADER LH  LEFT JOIN sysadm.TRANSLATION T1 ON 'LoadStatus.'+LH.STATUS = T1.ITEM_ID  AND T1.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T1.LANG+'-'+T1.COUNTRY = @LANGUAGE ";
        private string SQL_UPDATE_LOAD_HEADER = " UPDATE @_@LOAD_HEADER SET WH_ID = @WH_ID, EXTERNAL_ID = @EXTERNAL_ID, ROUTE = @ROUTE, DOOR = @DOOR, STATUS = @STATUS, CARRIER_ID = @CARRIER_ID, CARRIER_NAME = @CARRIER_NAME, CARRIER_ADDRESS = @CARRIER_ADDRESS, CARRIER_CITY = @CARRIER_CITY, CARRIER_PROV = @CARRIER_PROV, CARRIER_ZIP = @CARRIER_ZIP, CARRIER_COUNTRY = @CARRIER_COUNTRY, CARRIER_CONTACT = @CARRIER_CONTACT, CARRIER_PHONE = @CARRIER_PHONE, CARRIER_FAX = @CARRIER_FAX, CARRIER_EMAIL = @CARRIER_EMAIL, CARRIER_REF = @CARRIER_REF, VEHICLE_NUMBER = @VEHICLE_NUMBER, VEHICLE_TYPE = @VEHICLE_TYPE, TRAILER_NUMBER = @TRAILER_NUMBER, TRAILER_OWNER = @TRAILER_OWNER, DRIVER_NAME = @DRIVER_NAME, LOAD_BY = @LOAD_BY, BEGIN_LOAD_TIME = @BEGIN_LOAD_TIME, END_LOAD_TIME = @END_LOAD_TIME, DEPARTURE_TIME = @DEPARTURE_TIME, TOTAL_CUBE = @TOTAL_CUBE, TOTAL_WEIGHT = @TOTAL_WEIGHT, TOTAL_UNITS = @TOTAL_UNITS, EXPECTED_CUBE = @EXPECTED_CUBE, EXPECTED_WEIGHT = @EXPECTED_WEIGHT, EXPECTED_UNITS = @EXPECTED_UNITS, COST = @COST, VALUE = @VALUE, LOAD_SEQUENCE = @LOAD_SEQUENCE, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE LOAD_ID = @LOAD_ID  ";
        private string SQL_UPDATE_LOAD_STATUS = "UPDATE @_@LOAD_HEADER SET STATUS = @STATUS,UPDATED_DATE = GETDATE(),UPDATED_BY = @UPDATED_BY WHERE LOAD_ID = @LOAD_ID; UPDATE @_@LOAD_DETAIL SET STATUS = @STATUS,UPDATED_DATE = GETDATE(),UPDATED_BY = @UPDATED_BY WHERE LOAD_ID = @LOAD_ID; UPDATE @_@LOAD_PICK_DETAIL SET STATUS = @STATUS,UPDATED_DATE = GETDATE(),UPDATED_BY = @UPDATED_BY WHERE LOAD_ID = @LOAD_ID; UPDATE O SET O.STATUS = @STATUS,O.UPDATED_DATE = GETDATE(),O.UPDATED_BY = @UPDATED_BY  FROM @_@ORDERS O LEFT JOIN @_@LOAD_DETAIL LD ON O.ORDER_ID = LD.ORDER_ID WHERE LD.LOAD_ID = @LOAD_ID; UPDATE OD SET OD.STATUS = @STATUS,OD.UPDATED_DATE = GETDATE(),OD.UPDATED_BY = @UPDATED_BY  FROM @_@ORDER_DETAIL OD LEFT JOIN @_@LOAD_DETAIL LD ON OD.ORDER_ID = LD.ORDER_ID WHERE LD.LOAD_ID = @LOAD_ID;";
        private string STATUS = "@STATUS";
        private string TOTAL_CUBE = "@TOTAL_CUBE";
        private string TOTAL_UNITS = "@TOTAL_UNITS";
        private string TOTAL_WEIGHT = "@TOTAL_WEIGHT";
        private string TRAILER_NUMBER = "@TRAILER_NUMBER";
        private string TRAILER_OWNER = "@TRAILER_OWNER";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VALUE = "@VALUE";
        private string VEHICLE_NUMBER = "@VEHICLE_NUMBER";
        private string VEHICLE_TYPE = "@VEHICLE_TYPE";
        private string WH_ID = "@WH_ID";

        public bool CheckLoadHeaderIDUnique(string loadHeaderID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOAD_ID", loadHeaderID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_LOAD_HEADER_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckLoadHeaderIDUnique(DataBase dataBase, DbTransaction tran, string loadHeaderID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOAD_ID", loadHeaderID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_LOAD_HEADER_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteLoadHeader(List<string> loadHeaderID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < loadHeaderID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOAD_ID", loadHeaderID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_LOAD_HEADER.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteLoadHeader(DataBase dataBase, DbTransaction tran, List<string> loadHeaderID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < loadHeaderID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOAD_ID", loadHeaderID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_LOAD_HEADER.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllLoadHeader(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_LOAD_HEADER.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllLoadHeader(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_LOAD_HEADER.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public LoadHeaderInfo GetLoadHeaderByID(string loadheaderID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_LOAD_HEADER.Replace("@_@", whLoginID) + " WHERE LOAD_ID = @LOAD_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOAD_ID", loadheaderID) };
            LoadHeaderInfo loadHeaderInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    loadHeaderInfo = new LoadHeaderInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOAD_ID"].ToString(), reader["EXTERNAL_ID"].ToString(), reader["ROUTE"].ToString(), reader["DOOR"].ToString(), reader["STATUS"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_CONTACT"].ToString(), reader["CARRIER_PHONE"].ToString(), reader["CARRIER_FAX"].ToString(), reader["CARRIER_EMAIL"].ToString(), reader["CARRIER_REF"].ToString(), reader["VEHICLE_NUMBER"].ToString(), reader["VEHICLE_TYPE"].ToString(), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["DRIVER_NAME"].ToString(), reader["LOAD_BY"].ToString(), (reader["BEGIN_LOAD_TIME"].ToString() != "") ? DateTime.Parse(reader["BEGIN_LOAD_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["END_LOAD_TIME"].ToString() != "") ? DateTime.Parse(reader["END_LOAD_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["DEPARTURE_TIME"].ToString() != "") ? DateTime.Parse(reader["DEPARTURE_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["TOTAL_CUBE"].ToString() != "") ? ((double) float.Parse(reader["TOTAL_CUBE"].ToString())) : ((double) 0f), (reader["TOTAL_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["TOTAL_WEIGHT"].ToString())) : ((double) 0f), (reader["TOTAL_UNITS"].ToString() != "") ? decimal.Parse(reader["TOTAL_UNITS"].ToString()) : 0M, (reader["EXPECTED_CUBE"].ToString() != "") ? ((double) float.Parse(reader["EXPECTED_CUBE"].ToString())) : ((double) 0f), (reader["EXPECTED_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["EXPECTED_WEIGHT"].ToString())) : ((double) 0f), (reader["EXPECTED_UNITS"].ToString() != "") ? decimal.Parse(reader["EXPECTED_UNITS"].ToString()) : 0M, (reader["COST"].ToString() != "") ? ((double) float.Parse(reader["COST"].ToString())) : ((double) 0f), (reader["VALUE"].ToString() != "") ? ((double) float.Parse(reader["VALUE"].ToString())) : ((double) 0f), reader["LOAD_SEQUENCE"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return loadHeaderInfo;
        }

        public LoadHeaderInfo GetLoadHeaderByID(DataBase dataBase, DbTransaction tran, string loadheaderID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_LOAD_HEADER.Replace("@_@", whLoginID) + " WHERE LOAD_ID = @LOAD_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOAD_ID", loadheaderID) };
            LoadHeaderInfo loadHeaderInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                loadHeaderInfo = new LoadHeaderInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOAD_ID"].ToString(), reader["EXTERNAL_ID"].ToString(), reader["ROUTE"].ToString(), reader["DOOR"].ToString(), reader["STATUS"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_CONTACT"].ToString(), reader["CARRIER_PHONE"].ToString(), reader["CARRIER_FAX"].ToString(), reader["CARRIER_EMAIL"].ToString(), reader["CARRIER_REF"].ToString(), reader["VEHICLE_NUMBER"].ToString(), reader["VEHICLE_TYPE"].ToString(), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["DRIVER_NAME"].ToString(), reader["LOAD_BY"].ToString(), (reader["BEGIN_LOAD_TIME"].ToString() != "") ? DateTime.Parse(reader["BEGIN_LOAD_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["END_LOAD_TIME"].ToString() != "") ? DateTime.Parse(reader["END_LOAD_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["DEPARTURE_TIME"].ToString() != "") ? DateTime.Parse(reader["DEPARTURE_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["TOTAL_CUBE"].ToString() != "") ? ((double) float.Parse(reader["TOTAL_CUBE"].ToString())) : ((double) 0f), (reader["TOTAL_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["TOTAL_WEIGHT"].ToString())) : ((double) 0f), (reader["TOTAL_UNITS"].ToString() != "") ? decimal.Parse(reader["TOTAL_UNITS"].ToString()) : 0M, (reader["EXPECTED_CUBE"].ToString() != "") ? ((double) float.Parse(reader["EXPECTED_CUBE"].ToString())) : ((double) 0f), (reader["EXPECTED_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["EXPECTED_WEIGHT"].ToString())) : ((double) 0f), (reader["EXPECTED_UNITS"].ToString() != "") ? decimal.Parse(reader["EXPECTED_UNITS"].ToString()) : 0M, (reader["COST"].ToString() != "") ? ((double) float.Parse(reader["COST"].ToString())) : ((double) 0f), (reader["VALUE"].ToString() != "") ? ((double) float.Parse(reader["VALUE"].ToString())) : ((double) 0f), reader["LOAD_SEQUENCE"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return loadHeaderInfo;
        }

        public DataSet GetLoadHeaderByQueryList(List<string> sqlWhere, LoadHeaderQueryEntity loadheaderQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_LOAD_HEADER_LANGUAGE.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!loadheaderQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, loadheaderQuery.CurrentPage, loadheaderQuery.PageSize, loadheaderQuery.SortField, loadheaderQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetLoadHeaderByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, LoadHeaderQueryEntity loadheaderQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_LOAD_HEADER_LANGUAGE.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!loadheaderQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, loadheaderQuery.CurrentPage, loadheaderQuery.PageSize, loadheaderQuery.SortField, loadheaderQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertLoadHeader(LoadHeaderInfo loadHeaderInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                loadHeaderInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_LoadHeader_Parameters(loadHeaderInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_LOAD_HEADER.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertLoadHeader(DataBase dataBase, DbTransaction tran, LoadHeaderInfo loadHeaderInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                loadHeaderInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_LoadHeader_Parameters(loadHeaderInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_LOAD_HEADER.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_LoadHeader_Parameters(LoadHeaderInfo loadHeaderInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.LOAD_ID, SqlDbType.VarChar, 20), new SqlParameter(this.EXTERNAL_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ROUTE, SqlDbType.VarChar, 20), new SqlParameter(this.DOOR, SqlDbType.VarChar, 20), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_NAME, SqlDbType.VarChar, 50), new SqlParameter(this.CARRIER_ADDRESS, SqlDbType.VarChar, 200), new SqlParameter(this.CARRIER_CITY, SqlDbType.VarChar, 50), new SqlParameter(this.CARRIER_PROV, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.CARRIER_CONTACT, SqlDbType.VarChar, 30), new SqlParameter(this.CARRIER_PHONE, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_FAX, SqlDbType.VarChar, 20), 
                new SqlParameter(this.CARRIER_EMAIL, SqlDbType.VarChar, 60), new SqlParameter(this.CARRIER_REF, SqlDbType.VarChar, 20), new SqlParameter(this.VEHICLE_NUMBER, SqlDbType.VarChar, 20), new SqlParameter(this.VEHICLE_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.TRAILER_NUMBER, SqlDbType.VarChar, 30), new SqlParameter(this.TRAILER_OWNER, SqlDbType.VarChar, 30), new SqlParameter(this.DRIVER_NAME, SqlDbType.VarChar, 30), new SqlParameter(this.LOAD_BY, SqlDbType.VarChar, 20), new SqlParameter(this.BEGIN_LOAD_TIME, SqlDbType.DateTime, 8), new SqlParameter(this.END_LOAD_TIME, SqlDbType.DateTime, 8), new SqlParameter(this.DEPARTURE_TIME, SqlDbType.DateTime, 8), new SqlParameter(this.TOTAL_CUBE, SqlDbType.Float, 8), new SqlParameter(this.TOTAL_WEIGHT, SqlDbType.Float, 8), new SqlParameter(this.TOTAL_UNITS, SqlDbType.Decimal, 13), new SqlParameter(this.EXPECTED_CUBE, SqlDbType.Float, 8), new SqlParameter(this.EXPECTED_WEIGHT, SqlDbType.Float, 8), 
                new SqlParameter(this.EXPECTED_UNITS, SqlDbType.Decimal, 13), new SqlParameter(this.COST, SqlDbType.Float, 8), new SqlParameter(this.VALUE, SqlDbType.Float, 8), new SqlParameter(this.LOAD_SEQUENCE, SqlDbType.VarChar, 20), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(loadHeaderInfo.WhID))
            {
                paramArray[0].Value = loadHeaderInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = loadHeaderInfo.LoadID;
            if (!string.IsNullOrEmpty(loadHeaderInfo.ExternalID))
            {
                paramArray[2].Value = loadHeaderInfo.ExternalID;
            }
            else
            {
                paramArray[2].Value = DBNull.Value;
            }
            paramArray[3].Value = loadHeaderInfo.Route;
            if (!string.IsNullOrEmpty(loadHeaderInfo.Door))
            {
                paramArray[4].Value = loadHeaderInfo.Door;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            paramArray[5].Value = loadHeaderInfo.Status;
            if (!string.IsNullOrEmpty(loadHeaderInfo.CarrierID))
            {
                paramArray[6].Value = loadHeaderInfo.CarrierID;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.CarrierName))
            {
                paramArray[7].Value = loadHeaderInfo.CarrierName;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.CarrierAddress))
            {
                paramArray[8].Value = loadHeaderInfo.CarrierAddress;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.CarrierCity))
            {
                paramArray[9].Value = loadHeaderInfo.CarrierCity;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.CarrierProv))
            {
                paramArray[10].Value = loadHeaderInfo.CarrierProv;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.CarrierZip))
            {
                paramArray[11].Value = loadHeaderInfo.CarrierZip;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.CarrierCountry))
            {
                paramArray[12].Value = loadHeaderInfo.CarrierCountry;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.CarrierContact))
            {
                paramArray[13].Value = loadHeaderInfo.CarrierContact;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.CarrierPhone))
            {
                paramArray[14].Value = loadHeaderInfo.CarrierPhone;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.CarrierFax))
            {
                paramArray[15].Value = loadHeaderInfo.CarrierFax;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.CarrierEmail))
            {
                paramArray[0x10].Value = loadHeaderInfo.CarrierEmail;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.CarrierRef))
            {
                paramArray[0x11].Value = loadHeaderInfo.CarrierRef;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.VehicleNumber))
            {
                paramArray[0x12].Value = loadHeaderInfo.VehicleNumber;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.VehicleType))
            {
                paramArray[0x13].Value = loadHeaderInfo.VehicleType;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.TrailerNumber))
            {
                paramArray[20].Value = loadHeaderInfo.TrailerNumber;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.TrailerOwner))
            {
                paramArray[0x15].Value = loadHeaderInfo.TrailerOwner;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.DriverName))
            {
                paramArray[0x16].Value = loadHeaderInfo.DriverName;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.LoadBy))
            {
                paramArray[0x17].Value = loadHeaderInfo.LoadBy;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            DateTime CS00003 = new DateTime();
            if (loadHeaderInfo.BeginLoadTime != CS00003)
            {
                paramArray[0x18].Value = loadHeaderInfo.BeginLoadTime;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (loadHeaderInfo.EndLoadTime != new DateTime())
            {
                paramArray[0x19].Value = loadHeaderInfo.EndLoadTime;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            paramArray[0x1a].Value = loadHeaderInfo.DepartureTime;
            paramArray[0x1b].Value = loadHeaderInfo.TotalCube;
            paramArray[0x1c].Value = loadHeaderInfo.TotalWeight;
            paramArray[0x1d].Value = loadHeaderInfo.TotalUnits;
            paramArray[30].Value = loadHeaderInfo.ExpectedCube;
            paramArray[0x1f].Value = loadHeaderInfo.ExpectedWeight;
            paramArray[0x20].Value = loadHeaderInfo.ExpectedUnits;
            paramArray[0x21].Value = loadHeaderInfo.Cost;
            paramArray[0x22].Value = loadHeaderInfo.Value;
            if (!string.IsNullOrEmpty(loadHeaderInfo.LoadSequence))
            {
                paramArray[0x23].Value = loadHeaderInfo.LoadSequence;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.Udf1))
            {
                paramArray[0x24].Value = loadHeaderInfo.Udf1;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.Udf2))
            {
                paramArray[0x25].Value = loadHeaderInfo.Udf2;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.Udf3))
            {
                paramArray[0x26].Value = loadHeaderInfo.Udf3;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.Udf4))
            {
                paramArray[0x27].Value = loadHeaderInfo.Udf4;
            }
            else
            {
                paramArray[0x27].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.Udf5))
            {
                paramArray[40].Value = loadHeaderInfo.Udf5;
            }
            else
            {
                paramArray[40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.Udf6))
            {
                paramArray[0x29].Value = loadHeaderInfo.Udf6;
            }
            else
            {
                paramArray[0x29].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.Udf7))
            {
                paramArray[0x2a].Value = loadHeaderInfo.Udf7;
            }
            else
            {
                paramArray[0x2a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.Udf8))
            {
                paramArray[0x2b].Value = loadHeaderInfo.Udf8;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.Udf9))
            {
                paramArray[0x2c].Value = loadHeaderInfo.Udf9;
            }
            else
            {
                paramArray[0x2c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(loadHeaderInfo.Udf10))
            {
                paramArray[0x2d].Value = loadHeaderInfo.Udf10;
            }
            else
            {
                paramArray[0x2d].Value = DBNull.Value;
            }
            paramArray[0x2e].Value = loadHeaderInfo.CreatedBy;
            paramArray[0x2f].Value = loadHeaderInfo.UpdatedBy;
            return paramArray;
        }

        public string UpdateLoadAndOrderStatusByLoadID(string status, string loadID, string updateBy, string whLoginID)
        {
            string sql = this.SQL_UPDATE_LOAD_STATUS;
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updateBy), new SqlParameter("@LOAD_ID", loadID) };
            if (DBHelper.ExecuteNonQuery(CommandType.Text, sql.Replace("@_@", whLoginID), paras) > 0)
            {
                return loadID;
            }
            return "";
        }

        public string UpdateLoadAndOrderStatusByLoadID(DataBase dataBase, DbTransaction tran, string status, string loadID, string updateBy, string whLoginID)
        {
            string sql = this.SQL_UPDATE_LOAD_STATUS;
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updateBy), new SqlParameter("@LOAD_ID", loadID) };
            if (dataBase.ExecuteNonQuery(tran, CommandType.Text, sql.Replace("@_@", whLoginID), paras) > 0)
            {
                return loadID;
            }
            return "";
        }

        public int UpdateLoadHeader(LoadHeaderInfo loadHeaderInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                loadHeaderInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_LoadHeader_Parameters(loadHeaderInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_LOAD_HEADER.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateLoadHeader(DataBase dataBase, DbTransaction tran, LoadHeaderInfo loadHeaderInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                loadHeaderInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_LoadHeader_Parameters(loadHeaderInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_LOAD_HEADER.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

